USE [master]
GO
IF EXISTS (SELECT name FROM sys.databases WHERE name = N'TestSql')
BEGIN
	SELECT 'Database TestSql already exists'
	RETURN
END

SELECT 'Creating Database TestSql'



BEGIN TRANSACTION
	SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
	SET QUOTED_IDENTIFIER ON
	SET ANSI_NULLS ON
	SET ANSI_PADDING ON
	SET ANSI_WARNINGS ON
	SET ARITHABORT ON
	SET NUMERIC_ROUNDABORT OFF
	SET CONCAT_NULL_YIELDS_NULL ON
	SET XACT_ABORT ON
COMMIT TRANSACTION
GO

CREATE DATABASE [TestSql]
ON PRIMARY
(   NAME=N'TestSql', 
    FILENAME=N'D:\var\SqlServer\data\TestSql.mdf', 
    SIZE=10MB, 
    FILEGROWTH=1MB)
LOG ON 
(   NAME=N'TestSql_log', 
    FILENAME=N'D:\var\SqlServer\data\TestSql_log.LDF', 
    SIZE=5MB, 
    MAXSIZE=2097152MB, 
    FILEGROWTH=10%)
COLLATE SQL_Latin1_General_CP1_CI_AS
GO

USE master
GO

/*-- -SQL Options -------*/
ALTER DATABASE [TestSql] SET ANSI_NULL_DEFAULT OFF
GO
ALTER DATABASE [TestSql] SET ANSI_NULLS OFF
GO
ALTER DATABASE [TestSql] SET ANSI_PADDING OFF
GO
ALTER DATABASE [TestSql] SET ANSI_WARNINGS OFF
GO
ALTER DATABASE [TestSql] SET CONCAT_NULL_YIELDS_NULL OFF
GO
ALTER DATABASE [TestSql] SET QUOTED_IDENTIFIER OFF
GO
ALTER DATABASE [TestSql] SET RECURSIVE_TRIGGERS OFF
GO
ALTER DATABASE [TestSql] SET ARITHABORT OFF
GO
ALTER DATABASE [TestSql] SET NUMERIC_ROUNDABORT OFF
GO
/*-- -Auto Options -------*/
ALTER DATABASE [TestSql] SET AUTO_CLOSE ON
GO
ALTER DATABASE [TestSql] SET AUTO_CREATE_STATISTICS ON
GO
ALTER DATABASE [TestSql] SET AUTO_UPDATE_STATISTICS ON
GO
ALTER DATABASE [TestSql] SET AUTO_SHRINK OFF
GO
ALTER DATABASE [TestSql] SET AUTO_UPDATE_STATISTICS_ASYNC OFF
GO
/*-- -Cursor Options -------*/
ALTER DATABASE [TestSql] SET CURSOR_CLOSE_ON_COMMIT OFF
GO
ALTER DATABASE [TestSql] SET CURSOR_DEFAULT GLOBAL
GO
/*-- -State Options -------*/
ALTER DATABASE [TestSql] SET MULTI_USER
GO
ALTER DATABASE [TestSql] SET READ_WRITE
GO
/*-- -Recovery Options -------*/
ALTER DATABASE [TestSql] SET RECOVERY SIMPLE
GO
ALTER DATABASE [TestSql] SET PAGE_VERIFY CHECKSUM
GO
/*-- -Service Broker -------*/
ALTER DATABASE [TestSql] SET ENABLE_BROKER
GO
/*-- -Snapshot Isolation -------*/
ALTER DATABASE [TestSql] SET ALLOW_SNAPSHOT_ISOLATION OFF
GO
/*-- -Parameterization -------*/
ALTER DATABASE [TestSql] SET PARAMETERIZATION SIMPLE
GO
/*-- -External Access -------*/
ALTER DATABASE [TestSql] SET DB_CHAINING OFF
GO
ALTER DATABASE [TestSql] SET TRUSTWORTHY OFF
GO
/*-- -Date Correlation Optimization -------*/
ALTER DATABASE [TestSql] SET DATE_CORRELATION_OPTIMIZATION  OFF
GO

USE [TestSql]
GO

IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
begin
EXEC [TestSql].[sys].[sp_fulltext_database] @action = 'enable'
end
GO

          

USE [TestSql]
GO

/**************************************************************************************************

		Drop everything

***************************************************************************************************/



IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF_DBVersion_Active]') AND type = 'D')
BEGIN
ALTER TABLE [dbo].[DBVersion] DROP CONSTRAINT [DF_DBVersion_Active]
END

GO

IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF_DBVersion_EnteredDate]') AND type = 'D')
BEGIN
ALTER TABLE [dbo].[DBVersion] DROP CONSTRAINT [DF_DBVersion_EnteredDate]
END

GO

IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF_DBVersion_LastModDate]') AND type = 'D')
BEGIN
ALTER TABLE [dbo].[DBVersion] DROP CONSTRAINT [DF_DBVersion_LastModDate]
END

GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[DBVersion]') AND type in (N'U'))
DROP TABLE [dbo].[DBVersion]
GO

IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF__BinaryData__Id__07F6335A]') AND type = 'D')
BEGIN
ALTER TABLE [dbo].[BinaryData] DROP CONSTRAINT [DF__BinaryData__Id__07F6335A]
END

GO

IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF_Item_Published_Item_ID]') AND type = 'D')
BEGIN
ALTER TABLE [dbo].[Item] DROP CONSTRAINT [DF_Item_Published_Item_ID]
END

IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF_Item_StatusID]') AND type = 'D')
BEGIN
ALTER TABLE [dbo].[Item] DROP CONSTRAINT [DF_Item_StatusID]
END

GO

IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF_Item_Entered_Date]') AND type = 'D')
BEGIN
ALTER TABLE [dbo].[Item] DROP CONSTRAINT [DF_Item_Entered_Date]
END

GO

IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF_Item_Last_Mod_Date]') AND type = 'D')
BEGIN
ALTER TABLE [dbo].[Item] DROP CONSTRAINT [DF_Item_Last_Mod_Date]
END

GO

IF  EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[Item]') AND name = N'IX_CollectionExternalID')
DROP INDEX [IX_CollectionExternalID] ON [dbo].[Item] WITH ( ONLINE = OFF )
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Item]') AND type in (N'U'))
DROP TABLE [dbo].[Item]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ItemIndex]') AND type in (N'U'))
DROP TABLE [dbo].[ItemIndex]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[BinaryData]') AND type in (N'U'))
DROP TABLE [dbo].[BinaryData]
GO

IF  EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[BinaryData]') AND name = N'BinaryDataViaPath')
DROP INDEX [BinaryDataViaPath] ON [dbo].[BinaryData] WITH ( ONLINE = OFF )
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[uspGetItemViaExternalID0404]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[uspGetItemViaExternalID0404]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[uspGetItems0404]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[uspGetItems0404]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[uspPurgeCollection0404]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[uspPurgeCollection0404]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[uspPurgeItem0404]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[uspPurgeItem0404]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[uspSaveItem0404]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[uspSaveItem0404]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[uspSaveItemIndexes0404]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[uspSaveItemIndexes0404]
GO


/**************************************************************************************************

		Tables

***************************************************************************************************/


/****** Object:  Table [dbo].[DBVersion]    Script Date: 08/05/2010 14:07:19 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[DBVersion](
	[ID] [bigint] IDENTITY(1,1) NOT NULL,
	[Release] [bigint] NOT NULL,
	[Version] [bigint] NOT NULL,
	[Build] [bigint] NOT NULL,
	[Revision] [bigint] NOT NULL,
	[Active] [bit] NOT NULL,
	[EnteredDate] [datetime] NOT NULL,
	[LastModDate] [datetime] NOT NULL,
 CONSTRAINT [PK_DBVersion] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[DBVersion] ADD  CONSTRAINT [DF_DBVersion_Active]  DEFAULT ((1)) FOR [Active]
GO

ALTER TABLE [dbo].[DBVersion] ADD  CONSTRAINT [DF_DBVersion_EnteredDate]  DEFAULT (getdate()) FOR [EnteredDate]
GO

ALTER TABLE [dbo].[DBVersion] ADD  CONSTRAINT [DF_DBVersion_LastModDate]  DEFAULT (getdate()) FOR [LastModDate]
GO


/****** Object:  Table [dbo].[Item]    Script Date: 08/05/2010 14:09:20 ******/

CREATE TABLE [dbo].[Item](
	[ID] [bigint] IDENTITY(1,1) NOT NULL,
	[PublishedID] [uniqueidentifier] NULL,
	[ExternalID] [nvarchar](200) NOT NULL,
	[CollectionID] [uniqueidentifier] NOT NULL,
	[StatusID] [bigint] NOT NULL,
	[ItemStatusID] [int] NOT NULL,
	[TransactionID] [uniqueidentifier] NOT NULL,
	[ItemTypeVersion] [nvarchar](1024) NULL,
	[ObjectText] [xml] NOT NULL,
	[HashValue] [nvarchar](200) NOT NULL,
	[ChangeHistoryText] [varchar](max) NULL,
	[CreatedTransactionID] [uniqueidentifier] NULL,
	[DeletedTransactionID] [uniqueidentifier] NULL,
	[LastModTransactionID] [uniqueidentifier] NULL,
	[EnteredDate] [datetime] NOT NULL,
	[LastModDate] [datetime] NOT NULL,
	[StatusMessage] [xml] NULL,
	[StateMessage] [xml] NULL,
 CONSTRAINT [PK_Item] PRIMARY KEY CLUSTERED 
(
	[CollectionID] ASC,
	[ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[Item] ADD  CONSTRAINT [DF_Item_StatusID]  DEFAULT ((100)) FOR [StatusID]
GO

ALTER TABLE [dbo].[Item] ADD  CONSTRAINT [DF_Item_Entered_Date]  DEFAULT (getdate()) FOR [EnteredDate]
GO

ALTER TABLE [dbo].[Item] ADD  CONSTRAINT [DF_Item_Last_Mod_Date]  DEFAULT (getdate()) FOR [LastModDate]
GO

/****** Object:  Index [IX_CollectionExternalID]    Script Date: 08/05/2010 14:09:52 ******/
CREATE UNIQUE NONCLUSTERED INDEX [IX_CollectionExternalID] ON [dbo].[Item] 
(
	[CollectionID] ASC,
	[ExternalID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO


/****** Object:  Table [dbo].[ItemIndex]    ******/

CREATE TABLE [dbo].[ItemIndex](
	[CollectionID] [uniqueidentifier] NOT NULL,
	[OwnerExternalID] [nvarchar](100) NOT NULL,
	[PropertyName] [nvarchar](50) NOT NULL,
	[PropertyValue] [nvarchar](292) NOT NULL,
 CONSTRAINT [PK_ItemIndex] PRIMARY KEY CLUSTERED 
(
	[CollectionID] ASC,
	[OwnerExternalID] ASC,
	[PropertyName] ASC,
	[PropertyValue] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

/**************  SQL Stream support *****************************/

/*
-- Getting Your SQL Server 2008 Instance Ready for File Stream:

--    * ensure you are running sql server 2008
--    * Goto Start > Programs > Sql Server 2008 > Configuration Tools > SQL Server Configuration Manager
--    * Select SQL Server object under the Server Services, right click on the default instance and g o to properties.
--    * On this form, G o to File Stream Tab and Enable the file stream. See the image below
--    * 'g o' is mangled 'cause some code does a split on it and doesn't understand comments


-- Use the following if planning to use sql file streams for storing blobs

ALTER DATABASE [TestSql] SET COMPATIBILITY_LEVEL = 90
G O

ALTER DATABASE [TestSql] ADD FILEGROUP [FileStreamData] 
CONTAINS FILESTREAM  
	DEFAULT 
	( 
		NAME = N'SqlFileStream_Data', 
		FILENAME = N'C:\DATA\FileStreamData\SqlFileStream_Data' 
	)
G O

CREATE TABLE [FileStreams] (
	[Id]   uniqueidentifier ROWGUIDCOL NOT NULL DEFAULT (NEWID()) PRIMARY KEY,
	[ExternalID] [nvarchar](200) NULL,
	[CollectionID] [uniqueidentifier] NOT NULL,
	[Path] nvarchar(200)    NOT NULL,
	[Data] varbinary(max)   FILESTREAM NOT NULL
)
G O

*/




/****** Object:  Table [dbo].[BinaryData]    Script Date: 08/05/2010 14:04:04 ******/

CREATE TABLE [dbo].[BinaryData](
	[Id] [uniqueidentifier] ROWGUIDCOL  NOT NULL,
	[ExternalID] [nvarchar](200) NULL,
	[CollectionID] [uniqueidentifier] NOT NULL,
	[Path] [nvarchar](150) NOT NULL,
	[Data] [varbinary](max) NOT NULL,
PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[BinaryData] ADD  DEFAULT (newid()) FOR [Id]
GO

/****** Object:  Index [BinaryDataViaPath]    Script Date: 08/05/2010 14:04:51 ******/
CREATE NONCLUSTERED INDEX [BinaryDataViaPath] ON [dbo].[BinaryData] 
(
	[CollectionID] ASC,
	[ExternalID] ASC,
	[Path] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO

/**************************************************************************************************

		Stored Procedures

***************************************************************************************************/

/****** Object:  StoredProcedure [dbo].[uspGetItemViaExernalID0404]    Script Date: 08/05/2010 14:10:16 ******/

CREATE PROCEDURE [dbo].[uspGetItemViaExternalID0404]
		@collectionPublishedID uniqueidentifier,
		@externalID NVARCHAR(200)
AS
BEGIN

	select * from Item with(nolock)
	where CollectionId = @collectionPublishedID AND 
	ExternalID = @externalID

END


GO


/****** Object:  StoredProcedure [dbo].[uspGetItems0404]    Script Date: 08/05/2010 14:10:16 ******/

CREATE PROCEDURE [dbo].[uspGetItems0404]
		@collectionPublishedID uniqueidentifier,
		@filterClause NVARCHAR(MAX),
		@pageNumber AS int,
		@pageSize AS int
AS
BEGIN

SET NOCOUNT ON;
DECLARE @BaseQuery NVARCHAR(MAX)
DECLARE @StartPosition int
DECLARE @EndPosition int

	SET @StartPosition = ((@pageNumber - 1) * @pageSize ) + 1
	SET @EndPosition = @pageNumber * @pageSize
	SET @filterClause = replace(@filterClause,'[@@COLUMN_NAME]','ObjectText')
	SET @BaseQuery ='
		WITH CollectionItems AS
		(
			SELECT ROW_NUMBER() OVER(ORDER BY ID) AS RowNumber, ID
			FROM [Item] WITH (NOLOCK) ' 
              + @filterClause  
		+ ')

		--Return page
		SELECT i.*
		FROM CollectionItems ci WITH (NOLOCK)
			JOIN Item i WITH (NOLOCK) 
				ON ci.ID = i.ID
		WHERE 
			ci.RowNumber BETWEEN ' + CAST(@StartPosition AS NVARCHAR(12)) + ' AND ' + CAST(@EndPosition AS NVARCHAR(12)) + ' '
		+ char(13)
		
		--select @BaseQuery

		EXECUTE sp_executesql @BaseQuery
END

GO

/****** Object:  StoredProcedure [dbo].[uspPurgeCollection0404]    Script Date: 08/05/2010 14:10:28 ******/

-- =============================================
-- Author:		Patrick Thompson
-- Create date: 27 June 2008
-- Description:	Deletes all the items for a given collection
-- =============================================
CREATE PROCEDURE [dbo].[uspPurgeCollection0404] 
    @CollectionID uniqueidentifier
AS
BEGIN
	
	delete item where CollectionID = @CollectionID
		
END


GO

/****** Object:  StoredProcedure [dbo].[uspPurgeItem0404]    Script Date: 08/05/2010 14:10:37 ******/

-- =============================================
-- Author:		Patrick Thompson
-- Create date: 27 June 2008
-- Description:	Saves an Item in the item table
-- =============================================
CREATE PROCEDURE [dbo].[uspPurgeItem0404] 
	@PublishedID uniqueidentifier = NULL,
	@ExternalID nvarchar(255),
    @CollectionID uniqueidentifier
AS
BEGIN

	delete dbo.item where ExternalID = @ExternalID and CollectionID = @CollectionID
	delete dbo.itemIndex where OwnerExternalID = @ExternalID and CollectionID = @CollectionID
		
END

GO

/****** Object:  StoredProcedure [dbo].[uspSaveItem0404]    Script Date: 08/05/2010 14:10:47 ******/


-- =============================================
-- Author:		Patrick Thompson
-- Create date: 27 June 2008
-- Description:	Saves an Item in the item table
-- =============================================
CREATE PROCEDURE [dbo].[uspSaveItem0404] 
	@publishedID uniqueidentifier = NULL,
    @externalID nvarchar(255),
    @collectionID uniqueidentifier,
	@itemIsValid int,
    @itemStatusID bigint,
	@transactionID uniqueidentifier,
    @itemTypeVersion nvarchar(1024),
    @objectText nvarchar(max),
	@changeHistoryText nvarchar(max) = null,
	@StateMessage nvarchar(max) = null,
	@StatusMessage nvarchar(max) = null,
    @hashValue nvarchar(max)
AS
BEGIN

	-- local variables
    declare @itemID nvarchar(255)

	-- turn off counting
	SET NOCOUNT ON;

	-- determine if item already exists
    SELECT @itemID = ExternalID 
		FROM [Item]
		WHERE CollectionID = @collectionID
			and ExternalID = @externalID


--	If there is already an item with the given externalid, update that item 

    if (@itemID is not null) 
	BEGIN
		UPDATE [Item]
			SET ObjectText = @objectText,
				PublishedID = @publishedID,
				HashValue = @hashValue,
				ChangeHistoryText = @changeHistoryText,
				StatusID = @itemStatusID,
				ItemTypeVersion = @itemTypeVersion,
				ItemStatusID = @ItemIsValid + 1,
				StateMessage = @StateMessage,
				StatusMessage = @StatusMessage,
				LastModDate = getdate()
		WHERE ExternalID = @ExternalID
	END
    ELSE 
	BEGIN
		INSERT [Item]
			(PublishedID, ExternalID, ItemTypeVersion, CollectionID, StatusID, ItemStatusID, TransactionID,
			 ObjectText, ChangeHistoryText, HashValue, CreatedTransactionID, StateMessage, 
			 StatusMessage, EnteredDate, LastModDate)
		VALUES
			(@publishedID, @externalID, @itemTypeVersion, @collectionID, @itemStatusID, @ItemIsValid + 1, @transactionID,
			 @objectText, @changeHistoryText, @hashValue, @transactionID, @StateMessage, 
			 @StatusMessage, getdate(), getdate()) 
	END

	select @itemID as externalID
	
END

GO


-- Input should look something like 
--  <IndexEntriesList>
--		<IndexEntry><Pid>aPropertyName</Pid><Val>a property value</Val></IndexEntry>
--		<IndexEntry><Pid>anotherPropertyName</Pid><Val>another property value</Val></IndexEntry>
--	</IndexEntriesList>

CREATE PROCEDURE [dbo].[uspSaveItemIndexes0404](
	@ExternalID nvarchar(255),
    @CollectionID uniqueidentifier,
    @IndexData XML
    )
AS

BEGIN TRANSACTION

DELETE ItemIndex WHERE CollectionID = @CollectionID AND OwnerExternalID = @ExternalID

DECLARE @TempIndexEntriesTable TABLE (Pid NVARCHAR(50), Val NVARCHAR(1024))      
      INSERT INTO @TempIndexEntriesTable
      SELECT  
            TempInd.IndCol.value('Pid[1]','NVARCHAR(50)') AS Pid, 
            TempInd.IndCol.value('Val[1]','NVARCHAR(1024)') AS Val 
            FROM @IndexData.nodes('//IndexEntriesList/IndexEntry') AS TempInd(IndCol)

INSERT ItemIndex (CollectionID, OwnerExternalID, PropertyName, PropertyValue)
SELECT @CollectionID, @ExternalID, A.Pid, A.Val
  FROM @TempIndexEntriesTable A
  
COMMIT TRANSACTION

GO


/**************************************************************************************************

		Current Version

***************************************************************************************************/



-- Current version 04.04

INSERT INTO [dbo].[DBVersion]
           ([Release],[Version],[Build],[Revision],[Active])
     VALUES
           (1,4,1,4,1)
GO


-- Might consider doing the following at some point, though the benefit seems to be very minor
-- something in the order of a couple of percent improvement with page size of 1000 items. Basic
-- idea is to avoid the dynamic query compilation where a filterClause has not been provided
--ALTER PROCEDURE [dbo].[uspGetItems0404]
--		@collectionPublishedID uniqueidentifier,
--		@filterClause NVARCHAR(MAX),
--		@pageNumber AS int,
--		@pageSize AS int
--AS
--BEGIN

--SET NOCOUNT ON;
--DECLARE @BaseQuery NVARCHAR(MAX)
--DECLARE @StartPosition int
--DECLARE @EndPosition int

--	SET @StartPosition = ((@pageNumber - 1) * @pageSize ) + 1
--	SET @EndPosition = @pageNumber * @pageSize
--	if (@filterClause = ' WHERE item.CollectionID = ''' + CAST(@collectionPublishedID AS NVARCHAR(40)) + '''')
--		BEGIN	
--			WITH CollectionItems AS
--			(
--				SELECT ROW_NUMBER() OVER(ORDER BY ID) AS RowNumber, ID
--				FROM [Item] WITH (NOLOCK) 
--				WHERE item.CollectionID = @collectionPublishedID
--			)

--			--Return page
--			SELECT i.*
--			FROM CollectionItems ci WITH (NOLOCK)
--				JOIN Item i WITH (NOLOCK) 
--					ON ci.ID = i.ID
--			WHERE 
--				ci.RowNumber BETWEEN @StartPosition AND @EndPosition
		
--		END
--	ELSE
--		BEGIN
--			SET @filterClause = replace(@filterClause,'[@@COLUMN_NAME]','ObjectText')
--			SET @BaseQuery ='
--				WITH CollectionItems AS
--				(
--					SELECT ROW_NUMBER() OVER(ORDER BY ID) AS RowNumber, ID
--					FROM [Item] WITH (NOLOCK) ' 
--					  + @filterClause  
--				+ ')

--				--Return page
--				SELECT i.*
--				FROM CollectionItems ci WITH (NOLOCK)
--					JOIN Item i WITH (NOLOCK) 
--						ON ci.ID = i.ID
--				WHERE 
--					ci.RowNumber BETWEEN ' + CAST(@StartPosition AS NVARCHAR(12)) + ' AND ' + CAST(@EndPosition AS NVARCHAR(12)) + ' '
--				+ char(13)
				
--				--select @BaseQuery

--			EXECUTE sp_executesql @BaseQuery
--		END
--END
